﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using System.Transactions;

namespace SubSonic
{
    public class SqlQuery
    {
        public enum QueryType
        {
            Select,
            Update,
            Insert,
            Delete
        }

        public QueryType QueryCommandType = QueryType.Select;

        internal string[] SelectColumnList = new string[0];
        internal string SQLCommand = SqlFragment.SELECT;
        public string ProviderName = DataService.Provider.Name;
        public List<SubSonic.Constraint> Constraints = new List<SubSonic.Constraint>();
        internal TableSchema.TableCollection FromTables = new TableSchema.TableCollection();

        internal string TopSpec = "";
        public List<Join> Joins = new List<Join>();
        internal List<string> OrderBys = new List<string>();
        internal List<string> Expressions = new List<string>();
        public List<Aggregate> Aggregates = new List<Aggregate>();
        internal TableSchema.TableColumnCollection SelectColumns = new TableSchema.TableColumnCollection();

        internal int PageSize = 0;
        internal int CurrentPage = 0;
        public string IdColumn = "";

        internal DataProvider provider;

        internal string WorkingSql = "";

        public SqlQuery() {}

        public SqlQuery(DataProvider provider)
        {
            this.provider = provider;
        }


        #region Validation

        public virtual void ValidateQuery()
        {
            //gotta have a "FROM"
            if(FromTables.Count == 0)
                throw new SqlQueryException("Need to have at least one From table specified");
        }

        #endregion


        #region Constraints


        #region Where

        public Constraint Where(string columnName)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Where;
            c.ColumnName = columnName;
            c.query = this;
            return c;
        }

        public Constraint Where(TableSchema.TableColumn column)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Where;
            c.ColumnName = column.QualifiedName;
            c.query = this;
            return c;
        }

        public Constraint Where(Aggregate agg)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Where;
            c.ColumnName = agg.ToString();
            c.query = this;
            return c;
        }

        public Constraint WhereExpression(string columnName)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Where;
            c.ColumnName = "(" + columnName;
            c.query = this;
            return c;
        }

        #endregion


        #region Or

        public Constraint OrExpression(string columnName)
        {
            //as a convenience, check that the last constraint
            //is a close paren
            if(Constraints.Count > 0)
            {
                Constraint last = Constraints[Constraints.Count - 1];
                if(last.Comparison != Comparison.CloseParen)
                    CloseExpression();
            }
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Or;
            c.ColumnName = "(" + columnName;
            c.query = this;
            return c;
        }

        public Constraint Or(string columnName)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Or;
            c.ColumnName = columnName;
            c.query = this;
            return c;
        }

        public Constraint Or(TableSchema.TableColumn column)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Or;
            c.ColumnName = column.QualifiedName;
            c.query = this;
            return c;
        }

        public Constraint Or(Aggregate agg)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Or;
            c.ColumnName = agg.ToString();
            c.query = this;
            return c;
        }

        #endregion


        #region And

        public Constraint AndExpression(string columnName)
        {
            //as a convenience, check that the last constraint
            //is a close paren
            if(Constraints.Count > 0)
            {
                Constraint last = Constraints[Constraints.Count - 1];
                if(last.Comparison != Comparison.CloseParen)
                    CloseExpression();
            }
            Constraint c = new Constraint();
            c.Condition = ConstraintType.And;
            c.ColumnName = "(" + columnName;
            c.query = this;
            return c;
        }

        public Constraint And(string columnName)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.And;
            c.ColumnName = columnName;
            c.query = this;
            return c;
        }

        public Constraint And(TableSchema.TableColumn column)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.And;
            c.ColumnName = column.QualifiedName;
            c.query = this;
            return c;
        }

        public Constraint And(Aggregate agg)
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.And;
            c.ColumnName = agg.ToString();
            c.query = this;
            return c;
        }

        #endregion


        public SqlQuery CloseExpression()
        {
            Constraint c = new Constraint();
            c.Condition = ConstraintType.Where;
            c.Comparison = Comparison.CloseParen;
            c.ColumnName = "##";
            Constraints.Add(c);
            return this;
        }

        #endregion


        #region Exception Handling

        internal static SqlQueryException GenerateException(Exception fromException)
        {
            SqlQueryException result;
            //evaluate the error
            //sometimes these things are easy
            //and we can not only report back the problem
            //but how to solve

            //connection strings?
            if(fromException.Message.ToLower().Contains("user instance"))
            {
                result =
                    new SqlQueryException("You're trying to connect to a database in your App_Data directory, and your SQL Server installation does not support User Instances.",
                        fromException);
            }
            else if(fromException.Message.Contains("use correlation names"))
            {
                result =
                    new SqlQueryException(
                        "The joins on your query are not ordered properly - make sure you're not repeating a table in the first (or 'from') position on a join that's also specified in FROM. Also - a JOIN can't have two of the same table in the 'from' first position. Check the SQL output to see how to order this properly",
                        fromException);
            }
            else
                result = new SqlQueryException(fromException.Message, fromException);

            //

            return result;
        }

        #endregion


        #region object overrides

        /// <summary>
        /// Returns the currently set SQL statement for this query object
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return BuildSqlStatement();
        }

        #endregion


        #region Sql Builder

        internal ISqlGenerator GetGenerator()
        {
            ISqlGenerator generator = null;

            //decide the provider
            provider = DataService.Providers[ProviderName];

            //decide the generator
            //if(provider is MySqlDataProvider) {}
            //else 
            if(Utilities.Utility.IsSql2005(provider))
                generator = new Sql2005Generator(this);
            else
                generator = new ANSISqlGenerator(this);
            return generator;
        }

        public virtual string BuildSqlStatement()
        {
            ValidateQuery();
            ISqlGenerator generator = GetGenerator();
            string sql;

            if(QueryCommandType == QueryType.Update)
                sql = generator.BuildUpdateStatement();
            else if(QueryCommandType == QueryType.Insert)
                sql = generator.BuildInsertStatement();
            else if(QueryCommandType == QueryType.Delete)
                sql = generator.BuildDeleteStatement();
            else
            {
                if(PageSize > 0)
                    sql = generator.BuildPagedSelectStatement();
                else
                    sql = generator.BuildSelectStatement();
            }
            return sql;
        }

        #endregion


        #region Command Builder

        internal static void SetConstraintParams(SqlQuery qry, QueryCommand cmd)
        {
            //loop the constraints and add the values
            foreach(Constraint c in qry.Constraints)
            {
                if(c.Comparison == Comparison.BetweenAnd)
                {
                    cmd.Parameters.Add(c.ParameterName + "_start", c.StartValue);
                    cmd.Parameters.Add(c.ParameterName + "_end", c.EndValue);
                }
                else if(c.Comparison == Comparison.In)
                {
                    if (c.InSelect != null) {

                        //set the parameters for the nested select
                        //this will support recursive INs... I hope
                        SqlQuery.SetConstraintParams(c.InSelect, cmd);

                    } else {
                        int i = 1;
                        IEnumerator en = c.InValues.GetEnumerator();
                        while(en.MoveNext())
                        {
                            cmd.Parameters.Add("in" + i, en.Current);
                            i++;
                        }

                    }
                    

                    

                }
                else
                    cmd.Parameters.Add(c.ParameterName, c.ParameterValue);
            }
        }

        internal void SetConstraintParams(QueryCommand cmd)
        {
            //loop the constraints and add the values
            SetConstraintParams(this, cmd);
        }

        internal QueryCommand BuildCommand()
        {
            QueryCommand cmd = new QueryCommand(BuildSqlStatement(), provider.Name);
            SetConstraintParams(cmd);

            return cmd;
        }

        #endregion


        #region From

        public SqlQuery From(string tbl)
        {
            TableSchema.Table t = DataService.GetSchema(tbl, ProviderName);
            if(t != null)
                FromTables.Add(t);

            return this;
        }

        public SqlQuery From(TableSchema.Table tbl)
        {
            FromTables.Add(tbl);
            this.provider = tbl.Provider;
            return this;
        }

        public SqlQuery From<TSchema>() where TSchema : AbstractRecord<TSchema>, new() {
            TSchema item = new TSchema();
            TableSchema.Table tbl = item.GetSchema();
            FromTables.Add(tbl);
            return this;

        }
        #endregion


        #region Joins


        #region Join Builders

        private void CreateJoin(TableSchema.Table from, Join.JoinType type)
        {
            TableSchema.Table to;

            if(FromTables.Count > 0)
                to = FromTables[0];
            else
                throw new Exception("You must specify at least one FROM table so we know what to join on - use From()");

            Join j = new Join(from, to, type);

            
            //if(j != null)
                Joins.Add(j);
            //else
            //    throw new Exception("Can't find join columns for these tables - you might need to be explicit here, spcecifying the columns to join on instead");
        }

        private void CreateJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn, Join.JoinType type)
        {
            Join j = new Join(fromColumn, toColumn, type);
            Joins.Add(j);
        }

        private void CreateJoin(string fromTable, string fromColumn, string toTable, string toColumn, Join.JoinType type)
        {
            TableSchema.Table from = DataService.GetSchema(fromTable, provider.Name);
            TableSchema.Table to = DataService.GetSchema(toTable, provider.Name);

            //get the columns
            TableSchema.TableColumn fromCol = from.GetColumn(fromColumn);
            TableSchema.TableColumn toCol = to.GetColumn(toColumn);

            //set the join
            Join j = new Join(fromCol, toCol, type);

            Joins.Add(j);
        }

        #endregion


        #region Inner

        public SqlQuery InnerJoin(TableSchema.Table from)
        {
            CreateJoin(from, Join.JoinType.Inner);
            return this;
        }

        public SqlQuery InnerJoin(string tableName)
        {
            TableSchema.Table schema = DataService.GetSchema(tableName, provider.Name);
            return InnerJoin(schema);
        }

        public SqlQuery InnerJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn)
        {
            CreateJoin(fromColumn, toColumn, Join.JoinType.Inner);
            return this;
        }

        public SqlQuery InnerJoin(string fromTable, string fromColumn, string toTable, string toColumn)
        {
            CreateJoin(fromTable, fromColumn, toTable, toColumn, Join.JoinType.Inner);
            return this;
        }

        #endregion


        #region Outer

        public SqlQuery OuterJoin(TableSchema.Table from)
        {
            CreateJoin(from, Join.JoinType.Outer);
            return this;
        }

        public SqlQuery OuterJoin(string tableName)
        {
            TableSchema.Table schema = DataService.GetSchema(tableName, provider.Name);
            return OuterJoin(schema);
        }

        public SqlQuery OuterJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn)
        {
            CreateJoin(fromColumn, toColumn, Join.JoinType.Outer);
            return this;
        }

        public SqlQuery OuterJoin(string fromTable, string fromColumn, string toTable, string toColumn)
        {
            CreateJoin(fromTable, fromColumn, toTable, toColumn, Join.JoinType.Outer);
            return this;
        }

        #endregion


        #region Cross

        public SqlQuery CrossJoin(TableSchema.Table from)
        {
            CreateJoin(from, Join.JoinType.Cross);
            return this;
        }

        public SqlQuery CrossJoin(string tableName)
        {
            TableSchema.Table schema = DataService.GetSchema(tableName, provider.Name);
            CreateJoin(schema, Join.JoinType.Cross);
            return this;
        }

        public SqlQuery CrossJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn)
        {
            CreateJoin(fromColumn, toColumn, Join.JoinType.Cross);
            return this;
        }

        public SqlQuery CrossJoin(string fromTable, string fromColumn, string toTable, string toColumn)
        {
            CreateJoin(fromTable, fromColumn, toTable, toColumn, Join.JoinType.Cross);
            return this;
        }

        #endregion


        #region LeftInner

        public SqlQuery LeftInnerJoin(TableSchema.Table from)
        {
            CreateJoin(from, Join.JoinType.LeftInner);
            return this;
        }

        public SqlQuery LeftInnerJoin(string tableName)
        {
            TableSchema.Table schema = DataService.GetSchema(tableName, provider.Name);
            CreateJoin(schema, Join.JoinType.LeftInner);
            return this;
        }

        public SqlQuery LeftInnerJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn)
        {
            CreateJoin(fromColumn, toColumn, Join.JoinType.LeftInner);
            return this;
        }

        public SqlQuery LeftInnerJoin(string fromTable, string fromColumn, string toTable, string toColumn)
        {
            CreateJoin(fromTable, fromColumn, toTable, toColumn, Join.JoinType.LeftInner);
            return this;
        }

        #endregion


        #region RightInner

        public SqlQuery RightInnerJoin(TableSchema.Table from)
        {
            CreateJoin(from, Join.JoinType.RightInner);
            return this;
        }

        public SqlQuery RightInnerJoin(string tableName)
        {
            TableSchema.Table schema = DataService.GetSchema(tableName, provider.Name);
            CreateJoin(schema, Join.JoinType.RightInner);
            return this;
        }

        public SqlQuery RightInnerJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn)
        {
            CreateJoin(fromColumn, toColumn, Join.JoinType.RightInner);
            return this;
        }

        public SqlQuery RightInnerJoin(string fromTable, string fromColumn, string toTable, string toColumn)
        {
            CreateJoin(fromTable, fromColumn, toTable, toColumn, Join.JoinType.RightInner);
            return this;
        }

        #endregion


        #region LeftOuter

        public SqlQuery LeftOuterJoin(TableSchema.Table from)
        {
            CreateJoin(from, Join.JoinType.LeftOuter);
            return this;
        }

        public SqlQuery LeftOuterJoin(string tableName)
        {
            TableSchema.Table schema = DataService.GetSchema(tableName, provider.Name);
            CreateJoin(schema, Join.JoinType.LeftOuter);
            return this;
        }

        public SqlQuery LeftOuterJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn)
        {
            CreateJoin(fromColumn, toColumn, Join.JoinType.LeftOuter);
            return this;
        }

        public SqlQuery LeftOuterJoin(string fromTable, string fromColumn, string toTable, string toColumn)
        {
            CreateJoin(fromTable, fromColumn, toTable, toColumn, Join.JoinType.LeftOuter);
            return this;
        }

        #endregion


        #region RightOuter

        public SqlQuery RightOuterJoin(TableSchema.Table from)
        {
            CreateJoin(from, Join.JoinType.RightOuter);
            return this;
        }

        public SqlQuery RightOuterJoin(string tableName)
        {
            TableSchema.Table schema = DataService.GetSchema(tableName, provider.Name);
            CreateJoin(schema, Join.JoinType.RightOuter);
            return this;
        }

        public SqlQuery RightOuterJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn)
        {
            CreateJoin(fromColumn, toColumn, Join.JoinType.RightOuter);
            return this;
        }

        public SqlQuery RightOuterJoin(string fromTable, string fromColumn, string toTable, string toColumn)
        {
            CreateJoin(fromTable, fromColumn, toTable, toColumn, Join.JoinType.RightOuter);
            return this;
        }

        #endregion


        #region NotEqual

        public SqlQuery NotEqualJoin(TableSchema.Table from)
        {
            CreateJoin(from, Join.JoinType.NotEqual);
            return this;
        }

        public SqlQuery NotEqualJoin(string tableName)
        {
            TableSchema.Table schema = DataService.GetSchema(tableName, provider.Name);
            CreateJoin(schema, Join.JoinType.NotEqual);
            return this;
        }

        public SqlQuery NotEqualJoin(TableSchema.TableColumn fromColumn, TableSchema.TableColumn toColumn)
        {
            CreateJoin(fromColumn, toColumn, Join.JoinType.NotEqual);
            return this;
        }

        public SqlQuery NotEqualJoin(string fromTable, string fromColumn, string toTable, string toColumn)
        {
            CreateJoin(fromTable, fromColumn, toTable, toColumn, Join.JoinType.NotEqual);
            return this;
        }

        #endregion


        #endregion


        #region Ordering

        public SqlQuery OrderAsc(params string[] columns)
        {
            StringBuilder sb = new StringBuilder();
            foreach(string s in columns)
                sb.Append(s + " ASC");
            OrderBys.Add(sb.ToString());
            return this;
        }

        public SqlQuery OrderDesc(params string[] columns)
        {
            StringBuilder sb = new StringBuilder();
            foreach(string s in columns)
                sb.Append(s + " DESC");
            OrderBys.Add(sb.ToString());
            return this;
        }

        #endregion


        #region Paging

        public SqlQuery Paged(int currentPage, int pageSize)
        {
            CurrentPage = currentPage;
            PageSize = pageSize;
            return this;
        }

        public SqlQuery Paged(int currentPage, int pageSize, string idColumn)
        {
            CurrentPage = currentPage;
            PageSize = pageSize;
            IdColumn = idColumn;
            return this;
        }

        #endregion


        #region Execution

        public virtual int Execute()
        {
            int result;
            try
            {
                result = DataService.ExecuteQuery(BuildCommand());
            }
            catch(Exception x)
            {
                SqlQueryException ex = GenerateException(x);
                throw ex;
            }
            return result;
        }

        public virtual object ExecuteScalar()
        {
            object result;
            try
            {
                result = DataService.ExecuteScalar(BuildCommand());
            }
            catch(Exception x)
            {
                SqlQueryException ex = GenerateException(x);
                throw ex;
            }
            return result;
        }

        public virtual TResult ExecuteScalar<TResult>()
        {
            TResult result = default(TResult);

            try
            {
                object queryResult = DataService.ExecuteScalar(BuildCommand());

                if(queryResult != null)
                {
                    result = (TResult)Utilities.Utility.ChangeType(queryResult, typeof(TResult));
                    //result = (TResult)queryResult;
                }
            }
            catch(Exception x)
            {
                SqlQueryException ex = GenerateException(x);
                throw ex;
            }

            return result;
        }

        public virtual int GetRecordCount()
        {
            int count = 0;
            try
            {
                IDataReader rdr = ExecuteReader();
                while(rdr.Read())
                    count++;
                rdr.Close();
            }
            catch(Exception x)
            {
                SqlQueryException ex = GenerateException(x);
                throw ex;
            }

            return count;
        }

        public virtual IDataReader ExecuteReader()
        {
            try
            {
                IDataReader rdr = DataService.GetReader(BuildCommand());
            }
            catch(Exception x)
            {
                SqlQueryException ex = GenerateException(x);
                throw ex;
            }
            return DataService.GetReader(BuildCommand());
        }

        public virtual DataSet ExecuteDataSet()
        {
            DataSet ds;
            try
            {
                ds = DataService.GetDataSet(BuildCommand());
            }
            catch(Exception x)
            {
                SqlQueryException ex = GenerateException(x);
                throw ex;
            }
            return ds;
        }

        public virtual List<T> ExecuteTypedList<T>() where T : new()
        {

            List<T> result = new List<T>();
            System.Reflection.PropertyInfo[] props = null;
            IDataReader rdr = null;
            Type iType = typeof(T);

            try {
                rdr = this.ExecuteReader();
            } catch(Exception x) {
                SqlQueryException ex = GenerateException(x);
                throw ex;
            }

            if (iType is IActiveRecord) {
                
                //load it
                while (rdr.Read()) {
                    T item = new T();
                    //set to ActiveRecord
                    IActiveRecord arItem = (IActiveRecord)item;

                    arItem.Load(rdr);
                    result.Add(item);
                }
            } else {
                //coerce the values, using some light reflection
                props = iType.GetProperties();

                //set the values
                System.Reflection.PropertyInfo prop = null;
                while (rdr.Read()) {
                    T item = new T();
                    Type propType = null;

                    for(int i=0;i<rdr.FieldCount;i++){
                        prop = iType.GetProperty(rdr.GetName(i));
                        if (prop != null) {
                            prop.SetValue(item, rdr.GetValue(i), null);
                        }
                    }
                    result.Add(item);
                }
            }

            rdr.Close();

            return result;
        }

        public virtual ListType ExecuteAsCollection<ListType>()
            where ListType : IAbstractList, new()
        {
            ListType list = new ListType();
            try
            {
                IDataReader rdr = ExecuteReader();
                list.LoadAndCloseReader(rdr);
            }
            catch(Exception x)
            {
                SqlQueryException ex = GenerateException(x);
                throw ex;
            }

            return list;
        }

        #endregion


        #region Transactions
        public static void ExecuteTransaction(List<Insert> queries) {
            using (SharedDbConnectionScope scope = new SharedDbConnectionScope()) {
                using (TransactionScope ts = new TransactionScope()) {
                    foreach (Insert q in queries)
                        q.Execute();
                }
            }
        }
        public static void ExecuteTransaction(List<Insert> queries, DataProvider provider) {
            using (SharedDbConnectionScope scope = new SharedDbConnectionScope(provider)) {
                using (TransactionScope ts = new TransactionScope()) {
                    foreach (Insert q in queries)
                        q.Execute();
                }
            }
        }
        public static void ExecuteTransaction(List<Insert> queries, string connectionString) {
            using (SharedDbConnectionScope scope = new SharedDbConnectionScope(connectionString)) {
                using (TransactionScope ts = new TransactionScope()) {
                    foreach (Insert q in queries)
                        q.Execute();
                }
            }
        }

        public static void ExecuteTransaction(List<SqlQuery> queries)
        {
            using(SharedDbConnectionScope scope = new SharedDbConnectionScope())
            {
                using(TransactionScope ts = new TransactionScope())
                {
                    foreach(SqlQuery q in queries)
                        q.Execute();
                }
            }
        }

        public static void ExecuteTransaction(List<SqlQuery> queries, DataProvider provider)
        {
            using(SharedDbConnectionScope scope = new SharedDbConnectionScope(provider))
            {
                using(TransactionScope ts = new TransactionScope())
                {
                    foreach(SqlQuery q in queries)
                        q.Execute();
                }
            }
        }

        public static void ExecuteTransaction(List<SqlQuery> queries, string connectionString)
        {
            using(SharedDbConnectionScope scope = new SharedDbConnectionScope(connectionString))
            {
                using(TransactionScope ts = new TransactionScope())
                {
                    foreach(SqlQuery q in queries)
                        q.Execute();
                }
            }
        }

        public static void ExecuteTransaction(List<SqlQuery> queries, DataProvider provider, string connectionString)
        {
            using(SharedDbConnectionScope scope = new SharedDbConnectionScope(provider, connectionString))
            {
                using(TransactionScope ts = new TransactionScope())
                {
                    foreach(SqlQuery q in queries)
                        q.Execute();
                }
            }
        }

        #endregion
    }
}